#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import datetime
import os
import sys

import mysql.connector
import pandas as pd
from aliyun.log import LogClient, GetLogsRequest
from openpyxl import load_workbook

from tools import uc_center, uc_center_port
from tools import checkFile, get_specified_timestamps, format_timestamp


class AcceptedDetailInfo:
    def __init__(self, create_time, activity_id, animal_id, animal_name, animal_bet_num, prize, reward_animal_id,
                 reward_animal_name, cv):
        self.create_time = create_time
        self.activity_id = activity_id
        self.animal_id = animal_id
        self.animal_name = animal_name
        self.animal_bet_num = animal_bet_num
        self.prize = prize
        self.reward_animal_id = reward_animal_id
        self.reward_animal_name = reward_animal_name
        self.cv = cv

    def __str__(self):
        return f"""create_time:{self.create_time}, activity_id:{self.activity_id}, animal_name:{self.animal_name}, 
        animal_bet_num:{self.animal_bet_num}, prize:{self.prize}, reward_animal_id:{self.reward_animal_id},
        reward_animal_name:{self.reward_animal_name}, cv:{self.cv}"""


class UserAcceptedInfo:
    def __init__(self, uid, bet_num, prize, income):
        self.uid = uid
        self.bet_num = bet_num
        self.prize = prize
        self.income = income

    def __str__(self):
        return f"uid:{self.uid}, betNum:{self.bet_num}, prize:{self.prize}, income:{self.income}"


def fetch_user_accepted_info_with_time(start_time, end_time):
    connection = None
    cursor = None
    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(
            host=uc_center,
            user="dev_ro",
            password="uL0XTk8hjyRQ11FK",
            database="ucenter",
            port=uc_center_port
        )

        # 创建一个游标对象
        cursor = connection.cursor()

        # 执行查询
        query = f"""
        SELECT
		uid,
		SUM( animal_bet_num ) AS betNum,
		SUM(  COALESCE( prize, 0 )  ) AS prize,
		SUM(  COALESCE( prize, 0 )  ) - SUM( animal_bet_num ) AS income 
	    FROM
		uc_activity_ferris_wheel_user_record 
	    WHERE
		create_time BETWEEN {start_time} AND {end_time}
		AND uid NOT IN (
            1000014,1000015,1000079,1000079,1000251,1008465,10941724,10965098,10976377,
            10976377,11078796,11078798,11550144,11566429,11704405,11713484,11774473,
            11786352,11786352,11793957,11917833,11918721,11994647,12096378,12116191,
            1234421,12407745,12595859,13420655,13421443,13772075,13786205,13786540,
            13786725,13786725,13801020,1382857,14226097,14227714,14243022,14243071,
            14243394,1445425,14731824,14732048,15165726,15166199,15166856,15169124,
            15169124,15195000,15196873,1561552,1588335,15906077,15906129,1609467,
            16233296,16233296,16309255,16309396,1633044,1633365,1660885,1663629,
            1663647,1663661,1682181,1696773,1720199,1755189,1755435,1755562,1756499,
            1756677,1756677,1774008,1780606,1862850,1862919,1896678,1903532,1903532,
            1908693,1908693,1909532,1909532,1916847,1947218,1979612,2024786,2129592,
            2195628,2212390,2577077,2581916,2635462,2668982,2669147,2669161,2677006,
            2678608,2678709,2678715,2678915,2678932,2678949,2678971,2679102,2679131,
            2679222,2679311,2679321,2680733,2680753,2680784,2680796,2680975,2680975,
            2683081,2683140,2690531,2690539,2690549,2705794,2716029,2734003,2738435,
            2738435,2842850,2842884,2842902,2843210,2913068,2933956,3141207,3141207,
            3169896,3453279,3562877,3991682,3999691,3999691,3999691,4097606,4420576,
            4420576,4565754,4567544,4577977,4578026,4578121,4578149,4606913,4607469,
            4700578,4725624,4725713,4725851,4727061,4727482,4727517,4727545,4727573,
            4727594,4727813,4800863,625527498,6760013,6789785,7100114,7100114,7884742,
            7884742,7937621,7937621,7937631,7937647,7937647,7937651,7937662,7937672,
            7937688,7937699,7937699,7937712,7937712,7937712,7937714,7937714,7937717,
            7937728,7937728,7937740,7937740,7937740,7937755,7937758,7937774,7937806,
            7937811,7937820,8062089,8083893,8095978,8316140,8392630,8392630,8394479,
            8479254,8479254,8752481,8828395,8862241,8982410,9390737,9628342,6971547,2854683,15196873
	    ) 
	    GROUP BY
		uid
	    ORDER BY 
	    income DESC
        """
        cursor.execute(query)

        # 获取查询结果并封装成对象放入列表中
        results = cursor.fetchall()
        result_list = [UserAcceptedInfo(*row) for row in results]

        return result_list

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        return []

    finally:
        # 确保游标和连接被关闭
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def fetch_user_accepted_detail_info_with_time(uid, stat_time, end_time):
    connection = None
    cursor = None
    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(
            host=uc_center,
            user="dev_ro",
            password="uL0XTk8hjyRQ11FK",
            database="ucenter",
            port=uc_center_port
        )

        # 创建一个游标对象
        cursor = connection.cursor()
        # 执行查询
        query = f"""
            SELECT
            u.create_time,
		    u.activity_id AS activityId,
		    u.animal_id AS animalId,
		    animal.animalName AS animalName,
		    u.animal_bet_num AS animalBetNum,
            CAST(COALESCE(NULLIF(u.prize, ''), '0') AS UNSIGNED) AS prize,
            r.animal_id AS rewardAnimalId,
            animal2.animalName AS rewardAnimalName,
		    r.cv as CV
            FROM
            uc_activity_ferris_wheel_user_record u
            LEFT JOIN
            uc_activity_ferris_wheel_reward_record r
            ON u.activity_id = r.id
            LEFT JOIN uc_activity_ferris_wheel_animal animal
		    ON animal.id = u.animal_id

            LEFT JOIN uc_activity_ferris_wheel_animal animal2
            ON animal2.id = r.animal_id
            WHERE
            u.uid = {uid}
            AND u.create_time BETWEEN {stat_time} AND {end_time}
            """
        cursor.execute(query)

        # 获取查询结果并封装成对象放入列表中
        results = cursor.fetchall()
        result_list = [AcceptedDetailInfo(*row) for row in results]

        return result_list

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        return []

    finally:
        # 确保游标和连接被关闭
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def write_user_accepted_info_to_excel_pandas(user_accepted_infos, file_name="user_data.xlsx"):
    # 将UserAcceptedInfo对象列表转换为字典列表
    data = []
    for user_info in user_accepted_infos:
        data.append({
            "用户ID": user_info.uid,
            "下注钻石": user_info.bet_num,
            "赢取钻石": user_info.prize,
            "收入(赢取钻石-下注钻石)": user_info.income
        })

    # 创建DataFrame
    df = pd.DataFrame(data)

    # 使用ExcelWriter可以设置更多格式选项
    with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='用户数据', index=False)

        # 获取worksheet对象以进行格式调整
        worksheet = writer.sheets['用户数据']

        # 自动调整列宽
        for i, col in enumerate(df.columns):
            column_width = max(df[col].astype(str).map(len).max() * 2, (len(col) + 2) * 2)
            worksheet.column_dimensions[worksheet.cell(row=1, column=i + 1).column_letter].width = column_width

    print(f"数据已成功写入 {file_name}")


def write_accepted_detail_info_to_excel_pandas(accepted_detail_infos, file_name="user_detail_data.xlsx"):
    # 将AcceptedDetailInfo对象列表转换为字典列表
    data = []
    for accepted_info in accepted_detail_infos:
        data.append({
            "createTime": datetime.datetime.fromtimestamp(accepted_info.create_time).strftime("%Y-%m-%d %H:%M:%S"),
            "activityId": accepted_info.activity_id,
            "animalId": accepted_info.animal_id,
            "animalName": accepted_info.animal_name,
            "animalBetNum": accepted_info.animal_bet_num,
            "rewardAnimalId": accepted_info.reward_animal_id,
            "rewardAnimalName": accepted_info.reward_animal_name,
            "rewardAnimalPrize": accepted_info.prize,
            "CV": accepted_info.cv
        })

    # 创建DataFrame
    df = pd.DataFrame(data)

    # 使用ExcelWriter可以设置更多格式选项
    with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='用户数据', index=False)

        # 获取worksheet对象以进行格式调整
        worksheet = writer.sheets['用户数据']

        # 自动调整列宽
        for i, col in enumerate(df.columns):
            column_width = max(df[col].astype(str).map(len).max() + 5, len(col) + 5)
            worksheet.column_dimensions[worksheet.cell(row=1, column=i + 1).column_letter].width = column_width

    print(f"数据已成功写入 {file_name}")


def download_aliyun_log(start_time, end_time):
    end_time = end_time + 200

    # 初始化客户端
    accessKeyId = "LTAI4FssSMQ2cDnehiH8JHin"
    accessKey = "tX06dGLhTtbYHR3TK9CPkZ0Qe0X5Wc"
    endpoint = "me-east-1.log.aliyuncs.com"  # 替换为您的区域Endpoint
    client = LogClient(endpoint, accessKeyId, accessKey)

    # 定义Project和Logstore名称
    project_name = "xys-abroad-system"
    logstore_name = "common-log"

    query = "request_path : ferrisWheelRunLottery"
    offset = 0
    size = 100  # 每次获取的日志条数

    all_logs = []

    while True:
        request = GetLogsRequest(project_name, logstore_name, start_time, end_time, query=query, offset=offset,
                                 line=size)
        response = client.get_logs(request)
        logs_data = response.get_logs()

        if not logs_data:
            print(f"log为null")
            break

        for log in logs_data:
            result = {}
            for k, v in log.contents.items():
                match k:
                    case "activityId":
                        result["activityId"] = v
                    case "prompt":
                        result["prompt"] = v
            all_logs.append(result)

        offset += size

    print("日志查询完毕")
    return all_logs


def process_data(excel_file, all_logs, output_excel_file):
    # 读取Excel文件
    df = pd.read_excel(excel_file)

    # 创建一个字典，键为activityId，值为对应的prompt
    json_dict = {str(item['activityId']): {'prompt': item.get('prompt', '')} for item in all_logs}

    # 创建一个字典用于保存每个activityId的处理结果
    results = {}

    # 定义映射字典
    dict_win_name = {
        "100": "全水果", "101": "全肉", "1": "汉堡", "2": "烤串", "3": "火鸡",
        "4": "牛排", "5": "葡萄", "6": "西瓜", "7": "草莓", "8": "橘子"
    }

    odds_dict = {
        "1": 25, "2": 15, "3": 10, "4": 45, "5": 5,
        "6": 5, "7": 5, "8": 5
    }

    # 按activityId分组处理数据
    for activity_id, group in df.groupby('activityId'):
        activity_id_str = str(activity_id)

        # 从JSON获取prompt
        prompt = json_dict.get(activity_id_str, {}).get('prompt', '')

        # 获取rewardAnimalId (winId)
        reward_animal_id = group['rewardAnimalId'].iloc[0]
        win_id = reward_animal_id

        # 确定win_name
        win_name = dict_win_name.get(str(win_id), '')

        # 计算总投注额
        bet_total = group['animalBetNum'].sum()

        # 初始化prize和income
        prize = 0
        income = -bet_total  # 默认为负的总投注额

        # 确定LLM状态
        cv_value = group['CV'].iloc[0]
        llm_status = "兜底" if cv_value >= 0 else "大模型开奖"

        # 检查是否有中奖（animalId与winId相同的行）
        for _, row in group.iterrows():
            if win_id == 100:
                if int(row['animalId']) >= 5:
                    animal_bet_num = row['animalBetNum']
                    prize = animal_bet_num * odds_dict.get(str(row['animalId']), 0) + prize
                    income = prize - bet_total

            elif win_id == 101:
                if int(row['animalId']) <= 4:
                    animal_bet_num = row['animalBetNum']
                    prize = animal_bet_num * odds_dict.get(str(row['animalId']), 0) + prize
                    income = prize - bet_total
            else:
                if str(row['animalId']) == str(win_id):
                    animal_bet_num = row['animalBetNum']
                    odds = odds_dict.get(str(win_id), 0)
                    prize = animal_bet_num * odds
                    income = prize - bet_total
                    break

        # 存储处理结果
        results[activity_id] = {
            'prompt': prompt,
            'winId': win_id,
            'winName': win_name,
            'betTotal': bet_total,
            'prize': prize,
            'income': income,
            'llm': llm_status
        }

    # 准备将结果写回Excel
    # 由于我们需要合并单元格，无法仅使用pandas完成，所以仍需使用openpyxl

    # 加载工作簿
    wb = load_workbook(excel_file)
    ws = wb.active

    # 列的索引
    create_time = 1  # A列
    activity_id_col = 2  # B列
    animal_id_col = 3  # C列 - 元素id
    animal_name_col = 4  # D列 - 元素名称
    animal_bet_num_col = 5  # E列 - 元素下注金额
    reward_animal_id_col = 6  # F列 - rewardAnimalId
    reward_animal_name_col = 7  # G列
    reward_animal_prize_col = 8  # H列 每个元素收益
    cv_id_col = 9  # I列 - cv
    llm_col = 10  # J列 - llm
    prompt_col = 11  # K列 - prompt
    win_id_col = 12  # L列 - winId
    win_name_col = 13  # M列 - winName
    bet_total_col = 14  # N列 - betTotal
    prize_col = 15  # O列 - prize
    income_col = 16  # P列 - income

    ws.cell(row=1, column=llm_col).value = "llm"
    ws.cell(row=1, column=prompt_col).value = "prompt"
    ws.cell(row=1, column=win_id_col).value = "winId"
    ws.cell(row=1, column=win_name_col).value = "winName"
    ws.cell(row=1, column=bet_total_col).value = "betTotal"
    ws.cell(row=1, column=prize_col).value = "prize"
    ws.cell(row=1, column=income_col).value = "income"

    # 找出相同activityId的行
    activity_groups = {}
    for row in range(2, ws.max_row + 1):  # 从第2行开始（跳过标题行）
        activity_id = ws.cell(row=row, column=activity_id_col).value
        if activity_id not in activity_groups:
            activity_groups[activity_id] = []
        activity_groups[activity_id].append(row)

    # 合并单元格并填充数据
    for activity_id, rows in activity_groups.items():
        if activity_id in results and len(rows) > 0:
            result = results[activity_id]

            # 如果有多个行，需要合并单元格
            if len(rows) > 1:
                # 需要合并的列
                merge_columns = [
                    activity_id_col, reward_animal_id_col, reward_animal_name_col, llm_col, prompt_col,
                    win_id_col, win_name_col, bet_total_col, prize_col, income_col
                ]

                for col in merge_columns:
                    ws.merge_cells(
                        start_row=rows[0], start_column=col,
                        end_row=rows[-1], end_column=col
                    )

            # 填充数据
            ws.cell(row=rows[0], column=llm_col).value = result['llm']
            ws.cell(row=rows[0], column=prompt_col).value = result['prompt']
            ws.cell(row=rows[0], column=win_id_col).value = result['winId']
            ws.cell(row=rows[0], column=win_name_col).value = result['winName']
            ws.cell(row=rows[0], column=bet_total_col).value = result['betTotal']
            ws.cell(row=rows[0], column=prize_col).value = result['prize']
            ws.cell(row=rows[0], column=income_col).value = result['income']

    # 保存工作簿
    wb.save(output_excel_file)
    try:
        os.remove(excel_file)
    except Exception as e:
        print("删除文件失败")
        sys.exit(1)
    print(f"Excel文件已处理并保存为: {output_excel_file}")


def ferris_wheel_analysis():

    start_time, end_time = get_specified_timestamps()
    parent_dir_path = datetime.datetime.fromtimestamp(start_time).strftime("%Y-%m-%d")
    if not os.path.exists(parent_dir_path):
        # 创建新的文件夹
        try:
            os.mkdir(parent_dir_path)
            print(f"成功创建文件夹: {parent_dir_path}")
        except Exception as e:
            print(f"创建文件夹时出错: {e}")
            sys.exit(1)

    folder_name = "摩天轮"
    checkFile(os.path.join(parent_dir_path, folder_name))

    # 摩天轮每日所有用户的下注信息
    user_accepted_infos = fetch_user_accepted_info_with_time(start_time, end_time)
    if not user_accepted_infos:
        return

    daily_user_data_file = os.path.join(parent_dir_path, folder_name,
                                        f"摩天轮北京时间{format_timestamp(start_time)}到{format_timestamp(end_time)}所有用户下注收益信息统计.xlsx")
    write_user_accepted_info_to_excel_pandas(user_accepted_infos, daily_user_data_file)

    all_logs = download_aliyun_log(start_time, end_time)

    # 摩天轮每日赢钻石最多的用户的详细下注信息
    first_user_accepted_info = user_accepted_infos[0]
    user_id = first_user_accepted_info.uid
    user_data_file = os.path.join(parent_dir_path, folder_name,
                                  f"摩天轮北京时间{format_timestamp(start_time)}到{format_timestamp(end_time)}用户{user_id}下注收益信息.xlsx")
    process_user_data_file = os.path.join(parent_dir_path, folder_name,
                                          f"摩天轮北京时间{format_timestamp(start_time)}到{format_timestamp(end_time)}用户{user_id}下注收益信息_已处理.xlsx")
    user_accepted_detail_infos = fetch_user_accepted_detail_info_with_time(user_id, start_time, end_time)
    write_accepted_detail_info_to_excel_pandas(user_accepted_detail_infos, user_data_file)

    process_data(user_data_file, all_logs, process_user_data_file)

if __name__ == "__main__":
    ferris_wheel_analysis()
